create database WareHouse
go

use WareHouse
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AccessRecordInfo') and o.name = 'FK_ACCESSRE_REFERENCE_INVENTOR')
alter table AccessRecordInfo
   drop constraint FK_ACCESSRE_REFERENCE_INVENTOR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CommodityInfo') and o.name = 'FK_COMMODIT_REFERENCE_USERINFO')
alter table CommodityInfo
   drop constraint FK_COMMODIT_REFERENCE_USERINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DealInfo') and o.name = 'FK_DEALINFO_REFERENCE_USERINFO')
alter table DealInfo
   drop constraint FK_DEALINFO_REFERENCE_USERINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DealInfo') and o.name = 'FK_DEALINFO_REFERENCE_USERINFO')
alter table DealInfo
   drop constraint FK_DEALINFO_REFERENCE_USERINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('InventoryInfo') and o.name = 'FK_INVENTOR_REFERENCE_COMMODIT')
alter table InventoryInfo
   drop constraint FK_INVENTOR_REFERENCE_COMMODIT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('SellInfo') and o.name = 'FK_SELLINFO_REFERENCE_COMMODIT')
alter table SellInfo
   drop constraint FK_SELLINFO_REFERENCE_COMMODIT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('StockInfo') and o.name = 'FK_STOCKINF_REFERENCE_COMMODIT')
alter table StockInfo
   drop constraint FK_STOCKINF_REFERENCE_COMMODIT
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AccessRecordInfo')
            and   type = 'U')
   drop table AccessRecordInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CommodityInfo')
            and   type = 'U')
   drop table CommodityInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DealInfo')
            and   type = 'U')
   drop table DealInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('InventoryInfo')
            and   type = 'U')
   drop table InventoryInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SellInfo')
            and   type = 'U')
   drop table SellInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('StockInfo')
            and   type = 'U')
   drop table StockInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserInfo')
            and   type = 'U')
   drop table UserInfo
go

/*==============================================================*/
/* Table: AccessRecordInfo                                      */
/*==============================================================*/
create table AccessRecordInfo (
   AccessRecordId       int                  not null,
   InventoryId          int                  not null,
   InCount              int                  not null default 0
      constraint CKC_INCOUNT_ACCESSRE check (InCount >= 0),
   OutCount             int                  not null default 0
      constraint CKC_OUTCOUNT_ACCESSRE check (OutCount >= 0),
   AccessRecordTime     smalldatetime        not null default getdate(),
   constraint PK_ACCESSRECORDINFO primary key (AccessRecordId)
)
go

/*==============================================================*/
/* Table: CommodityInfo                                         */
/*==============================================================*/
create table CommodityInfo (
   CommodityId          int                  identity(1,1) not for replication,
   UserId               int                  not null,
   CommodityName        nvarchar(30)         not null,
   Supplier             nvarchar(30)         not null,
   Supply               int                  not null default 0
      constraint CKC_SUPPLY_COMMODIT check (Supply >= 0),
   CommodityMoney       money                not null default 0
      constraint CKC_COMMODITYMONEY_COMMODIT check (CommodityMoney >= 0),
   CommodityTime        smalldatetime        not null default getdate(),
   constraint PK_COMMODITYINFO primary key (CommodityId)
)
go

/*==============================================================*/
/* Table: DealInfo                                              */
/*==============================================================*/
create table DealInfo (
   DealId               int                  identity(1,1) not for replication,
   CommodityId          int                  not null,
   SellUser             int                  not null,
   BuyUser              int                  not null,
   DealMoney            money                not null default 0
      constraint CKC_DEALMONEY_DEALINFO check (DealMoney >= 0),
   DealTime             smalldatetime        not null default getdate(),
   constraint PK_DEALINFO primary key (DealId)
)
go

/*==============================================================*/
/* Table: InventoryInfo                                         */
/*==============================================================*/
create table InventoryInfo (
   InventoryId          int                  identity(1,1) not for replication,
   CommodityId          int                  not null,
   InventoryCount       int                  not null default 0
      constraint CKC_INVENTORYCOUNT_INVENTOR check (InventoryCount >= 0),
   constraint PK_INVENTORYINFO primary key (InventoryId)
)
go

/*==============================================================*/
/* Table: SellInfo                                              */
/*==============================================================*/
create table SellInfo (
   SellId               int                  identity(1,1) not for replication,
   CommodityId          int                  not null,
   SellCount            int                  not null default 0
      constraint CKC_SELLCOUNT_SELLINFO check (SellCount >= 0),
   SellMoney            money                not null default 0
      constraint CKC_SELLMONEY_SELLINFO check (SellMoney >= 0),
   SellTime             smalldatetime        not null default getdate(),
   constraint PK_SELLINFO primary key (SellId)
)
go

/*==============================================================*/
/* Table: StockInfo                                             */
/*==============================================================*/
create table StockInfo (
   StockId              int                  identity(1,1) not for replication,
   CommodityId          int                  not null,
   StockCount           int                  not null default 0
      constraint CKC_STOCKCOUNT_STOCKINF check (StockCount >= 0),
   StockMoney           money                not null default 0
      constraint CKC_STOCKMONEY_STOCKINF check (StockMoney >= 0),
   StockTime            smalldatetime        not null default getdate(),
   constraint PK_STOCKINFO primary key (StockId)
)
go

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
   UserId               int                  identity(1,1) not for replication,
   UserName             varchar(30)          not null,
   UserPwd              varchar(30)          not null,
   RealName             nvarchar(10)         not null,
   UserMoney            money                not null default 0
      constraint CKC_USERMONEY_USERINFO check (UserMoney >= 0),
   UserTime             smalldatetime        not null default getdate(),
   constraint PK_USERINFO primary key (UserId)
)
go

alter table AccessRecordInfo
   add constraint FK_ACCESSRE_REFERENCE_INVENTOR foreign key (InventoryId)
      references InventoryInfo (InventoryId)
go

alter table CommodityInfo
   add constraint FK_COMMODIT_REFERENCE_USERINFO foreign key (UserId)
      references UserInfo (UserId)
go

alter table DealInfo
   add constraint FK_DEALINFO_REFERENCE_USERINFO foreign key (SellUser)
      references UserInfo (UserId)
go

alter table DealInfo
   add constraint FK_DEALINFO_REFERENCE_USERINFO_1 foreign key (BuyUser)
      references UserInfo (UserId)
go

alter table InventoryInfo
   add constraint FK_INVENTOR_REFERENCE_COMMODIT foreign key (CommodityId)
      references CommodityInfo (CommodityId)
go

alter table SellInfo
   add constraint FK_SELLINFO_REFERENCE_COMMODIT foreign key (CommodityId)
      references CommodityInfo (CommodityId)
go

alter table StockInfo
   add constraint FK_STOCKINF_REFERENCE_COMMODIT foreign key (CommodityId)
      references CommodityInfo (CommodityId)
go
